package com.sh.data.engine.domain.common.service.impl;

import com.google.common.collect.Lists;
import com.sh.data.engine.common.enumDefinition.DSType;
import com.sh.data.engine.common.exception.BusinessException;
import com.sh.data.engine.domain.base.model.enums.Database;
import com.sh.data.engine.domain.common.model.domain.SimpleTableFieldDomain;
import com.sh.data.engine.domain.common.service.CommonService;
import com.sh.data.engine.domain.dataasset.service.TblPropService;
import com.sh.data.engine.domain.integration.datasource.model.domain.DataSourceDetailDomain;
import com.sh.data.engine.domain.integration.datasource.service.DataSourceService;
import com.sh.data.engine.domain.integration.offline.model.domain.OfflineSyncRedisDomain;
import com.sh.data.engine.domain.shims.DbManagerFactory;
import com.sh.data.engine.domain.shims.db.BaseDbManager;
import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.postgresql.manager.PostgreSqlManager;
import com.sh.data.engine.domain.workspace.index.service.ProjectService;
import com.sh.data.engine.domain.workspace.manager.service.impl.StorageServiceImpl;
import com.sh.data.engine.infrastructure.config.InnerDataSourceConfig;
import com.sh.data.engine.infrastructure.config.XxlJobProperties;
import com.sh.data.engine.job.admin.core.model.XxlJobGroup;
import com.sh.data.engine.job.admin.dao.XxlJobGroupDao;
import com.sh.data.engine.repository.dao.dataasset.entity.TblPropEntity;
import com.sh.data.engine.repository.dao.integration.datasource.entity.DataSourceEntity;
import com.sh.data.engine.repository.dao.workspace.index.entity.ProjectEntity;
import com.sh.data.engine.repository.dao.workspace.manager.entity.StorageEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

import java.lang.management.ManagementFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.stream.Collectors;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/20 20:59
 */
@Service
@Slf4j
public class CommonServiceImpl implements CommonService {

    @Value("${data.engine.availableMemoryUsed:20}")
    private Integer availableMemoryUsed;

    @Lazy
    @Autowired
    private StorageServiceImpl storageServiceImpl;

    @Lazy
    @Autowired
    private ProjectService projectService;

    @Lazy
    @Autowired
    private TblPropService tblPropService;

    @Autowired
    private XxlJobGroupDao xxlJobGroupDao;

    @Autowired
    private XxlJobProperties xxlJobProperties;

    @Lazy
    @Autowired
    private DataSourceService dataSourceService;

    @Autowired
    private RedisTemplate<String, String> redisTemplate;

    @Autowired
    private InnerDataSourceConfig innerDataSourceConfig;

    @Override
    public BaseDbManager getStorageDbManagerByProjctId(Long projectId) {
        StorageEntity storage = storageServiceImpl.getStorageInfoByProjectId(projectId);
        BaseDbManager dbManager =
            getDbManager(
                storage.getStorageType(),
                storage.getRmdbs().getUsername(),
                storage.getRmdbs().getPassword(),
                storage.getStorageLink(),
                storage.getRmdbs().getHiveSiteXmlPath(),
                storage.getRmdbs().getSchema());

        return dbManager;
    }


    @Override
    public BaseDbManager getPgManager() {
        DbOptions dbOptions = new DbOptions();
        dbOptions.setDatabase(Database.POSTGRESQL);
        dbOptions.setSchema(innerDataSourceConfig.getSchema());
        dbOptions.setUrl(innerDataSourceConfig.getUrl());
        dbOptions.setUsername(innerDataSourceConfig.getUsername());
        dbOptions.setPassword(innerDataSourceConfig.getPassword());

        final BaseDbManager dbManager = DbManagerFactory.getDbManager(Database.POSTGRESQL, dbOptions);

        return dbManager;
    }

    @Override
    public StorageEntity getStorage(Long projectId) {
        ProjectEntity project = projectService.getProjectById(projectId);
        if (null == project) {
            throw new BusinessException("检查空间状态");
        }
        return storageServiceImpl.getStorageInfoByProjectId(project.getId());
    }

    @Override
    public List<TableInfoDomain> getTblsByProjectId(Long projectId) {
        BaseDbManager dbManager = getStorageDbManagerByProjctId(projectId);
        StorageEntity storage = getStorage(projectId);
        List<TableInfoDomain> tableList = Lists.newArrayList();
        try {
            if (dbManager instanceof PostgreSqlManager) {
                tableList = dbManager.getTableList(storage.getRmdbs().getSchema());
            } else {
                tableList = dbManager.getTableList(storage.getRmdbs().getDbName());
            }
        } catch (Exception e) {

        } finally {
            dbManager.close();
        }
        return tableList;
    }

    @Override
    public TableInfoDomain getTblInfoByTblName(Long projectId, String tblName) {
        BaseDbManager dbManager = getStorageDbManagerByProjctId(projectId);
        StorageEntity storage = getStorage(projectId);
        TableInfoDomain tblInfo = null;
        try {
            if (dbManager instanceof PostgreSqlManager) {
                tblInfo = dbManager.getTableInfoByTableName(storage.getRmdbs().getSchema(), tblName);
            } else {
                tblInfo = dbManager.getTableInfoByTableName(storage.getRmdbs().getDbName(), tblName);
            }
        } catch (Exception e) {

        } finally {
            dbManager.close();
        }
        return tblInfo;
    }

    @Override
    public TableInfoDomain getTblInfoByTblId(Long projectId, Long tblId) {
        TblPropEntity tblPropByTblId = tblPropService.getTblPropByTblId(projectId, tblId);
        String tableName = tblPropByTblId.getTableName();
        return getTblInfoByTblName(projectId, tableName);
    }

    /**
     * 根据项目id，表名前缀获取表信息及字段信息
     *
     * @param projectDId
     * @param prefix
     * @return
     */
    @Override
    public List<SimpleTableFieldDomain> getSimpleTableFieldInfoByProjectIdAndPrefix(
        Long projectDId, String prefix) {
        return null;
    }

    public List<SimpleTableFieldDomain> ImplgetSimpleTableFieldInfoByProjectIdAndPrefix(
        Long projectId, String prefix) {
        List<TblPropEntity> tblProps = tblPropService.getTblPropByProjectId(projectId);
        List<TblPropEntity> tblForPrefix =
            tblProps.stream()
                .filter(
                    i -> {
                        String tableName = i.getTableName();
                        String[] s = tableName.split("_");
                        if (s.length > 1) {
                            if (Objects.equals(s[0], prefix)) {
                                return true;
                            }
                        }
                        return false;
                    })
                .collect(Collectors.toList());
        List<SimpleTableFieldDomain> list = Lists.newArrayList();
        tblForPrefix.forEach(
            i -> {
                TableInfoDomain tblInfoByTblId = getTblInfoByTblId(projectId, i.getTblId());
                List<FieldInfoDomain> filedInfosByTblName =
                    getFiledInfosByTblName(projectId, i.getTableName());
                SimpleTableFieldDomain simpleTableFieldDomain =
                    new SimpleTableFieldDomain(tblInfoByTblId, filedInfosByTblName);
                list.add(simpleTableFieldDomain);
            });
        return list;
    }

    @Override
    public SimpleTableFieldDomain getSimpleTableFieldInfo(Long projetId, Long tblId) {
        TblPropEntity tblPropByTblId = tblPropService.getTblPropByTblId(projetId, tblId);
        String tableName = tblPropByTblId.getTableName();
        TableInfoDomain tblInfoByTblId = getTblInfoByTblId(projetId, tblId);
        List<FieldInfoDomain> filedInfosByTblName = getFiledInfosByTblName(projetId, tableName);
        return new SimpleTableFieldDomain(tblInfoByTblId, filedInfosByTblName);
    }

    @Override
    public List<FieldInfoDomain> getTableFieldListByTblId(Long projectId, Long tblId) {
        TblPropEntity tblPropByTblId = tblPropService.getTblPropByTblId(projectId, tblId);
        String tableName = tblPropByTblId.getTableName();

        return getFiledInfosByTblName(projectId, tableName);
    }

    @Override
    public XxlJobGroup getXxlDefaultGroup() {
        final List<XxlJobGroup> list = xxlJobGroupDao.findAll();

        if (CollectionUtils.isEmpty(list)) {
            return null;
        }

        final String appname = xxlJobProperties.getJob().getExecutor().getAppname();

        if (StringUtils.isBlank(appname)) {
            return null;
        }

        final Optional<XxlJobGroup> jobGroupOptional =
            list.stream().filter(e -> e.getAppname().equals(appname)).findFirst();

        if (!jobGroupOptional.isPresent()) {
            return null;
        }

        return jobGroupOptional.get();
    }

    @Override
    public DataSourceEntity getSystemDateSource(Long projectId) {
        DataSourceEntity systemDataSourceDetail =
            dataSourceService.getSystemDataSourceDetail(projectId);
        return systemDataSourceDetail;
    }

    @Override
    public void getTempCreateTable(OfflineSyncRedisDomain offlineSyncRedisDomain) {
        Long dsId = offlineSyncRedisDomain.getDsId();
        String dsType = offlineSyncRedisDomain.getDsType();
        String database = offlineSyncRedisDomain.getDatabase();
        String targetTable = offlineSyncRedisDomain.getTargetTable();
        String sourceTable = offlineSyncRedisDomain.getSourceTable();
        String[] split = sourceTable.split("\\.");
        String[] split2 = targetTable.split("\\.");
        if (dsType.equalsIgnoreCase(DSType.PostgreSQL.name())) {
            String createNewTableTemplate =
                "create table if not exists %s (like %s INCLUDING INDEXES INCLUDING COMMENTS)";
            // String dumpPgTable =
            //     "SELECT\n"
            //         + "    'CREATE TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname)
            // || E' (\\n' ||\n"
            //         + "    array_to_string(\n"
            //         + "        array_agg(\n"
            //         + "            '    ' || quote_ident(a.attname) || ' ' || format_type(a.atttypid,
            // a.atttypmod) ||\n"
            //         + "            CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||\n"
            //         + "            CASE WHEN ad.adsrc LIKE 'nextval%' THEN ' PRIMARY KEY' ELSE ''
            // END\n"
            //         + "        ),\n"
            //         + "        E',\\n'\n"
            //         + "    ) || E'\\n);'\n"
            //         + "FROM\n"
            //         + "    pg_class c\n"
            //         + "    JOIN pg_namespace n ON n.oid = c.relnamespace\n"
            //         + "    JOIN pg_attribute a ON a.attrelid = c.oid\n"
            //         + "    LEFT JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum =
            // a.attnum\n"
            //         + "WHERE\n"
            //         + "    c.relname = \'"
            //         + split[1]
            //         + "\'\n"
            //         + "    AND n.nspname = \'"
            //         + split[0]
            //         + "\'\n"
            //         + "    AND a.attnum > 0\n"
            //         + "GROUP BY\n"
            //         + "    n.nspname,\n"
            //         + "    c.relname,\n"
            //         + "    c.relkind,\n"
            //         + "    c.relowner,\n"
            //         + "    c.reloptions\n"
            //         + "ORDER BY\n"
            //         + "    n.nspname,\n"
            //         + "    c.relname;\n";
            // String newCreateTable = null;

            DataSourceDetailDomain dataSourceDetailById =
                dataSourceService.getDataSourceDetailById(dsId, false);
            BaseDbManager dbManager =
                getDbManager(
                    dsType,
                    dataSourceDetailById.getUsername(),
                    dataSourceDetailById.getPassword(),
                    dataSourceDetailById.getDsLink());

            // try (Connection connection = dbManager.getConnection();
            //     PreparedStatement pstmt = createPreparedStatement(connection, dumpPgTable);
            //     ResultSet rs = pstmt.executeQuery()) {
            //   while (rs.next()) {
            //     newCreateTable = rs.getString("?column?");
            //   }
            // } catch (SQLException e) {
            //   log.error(e.getMessage());
            // }
            // if (StringUtils.isBlank(newCreateTable)){
            //   throw new BusinessException(StatusCode.FAILED,"表在数据库不存在，请重新确认");
            // }

            String s = String.format(createNewTableTemplate, targetTable, sourceTable);
            // String s = newCreateTable.replaceAll(split[1], split2[1]);
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt = connection.prepareStatement(s);
                 ResultSet rs = pstmt.executeQuery()) {
            } catch (SQLException e) {
                log.error("pg建表失败：" + e.getMessage());
            }
            log.info("pg建表成功,{},{},{}", s, sourceTable, targetTable);
        }
    }

    private static PreparedStatement createPreparedStatement(
        Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement statement = conn.prepareStatement(sql);
        if (params == null || params.length == 0) {
            return statement;
        }
        for (int index = 0; index < params.length; index++) {
            Object param = params[index];
            if (param instanceof String) {
                statement.setString(index + 1, (String) param);
            } else if (param instanceof Long) {
                statement.setLong(index + 1, (Long) param);
            } else if (param instanceof Integer) {
                statement.setInt(index + 1, (Integer) param);
            } else {
                throw new SQLException("not support param type ");
            }
        }
        return statement;
    }

    @Override
    public void executeChangeTable(OfflineSyncRedisDomain offlineSyncRedisDomain) {
        Long dsId = offlineSyncRedisDomain.getDsId();
        String dsType = offlineSyncRedisDomain.getDsType();
        String database = offlineSyncRedisDomain.getDatabase();
        String targetTable = offlineSyncRedisDomain.getTargetTable();
        String sourceTable = offlineSyncRedisDomain.getSourceTable();
        BaseDbManager dbManager = null;
        if (DSType.PostgreSQL.name().equalsIgnoreCase(dsType)) {
            DataSourceDetailDomain dataSourceDetailById =
                dataSourceService.getDataSourceDetailById(dsId, false);
            dbManager =
                getDbManager(
                    dsType,
                    dataSourceDetailById.getUsername(),
                    dataSourceDetailById.getPassword(),
                    dataSourceDetailById.getDsLink());

            String checkExternal =
                "SELECT relname, relkind\n"
                    + "FROM pg_class\n"
                    + "WHERE relname = ? \n"
                    + "AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =? )\n";
            String table = null;
            String schema = null;
            String originTable = null;
            if (sourceTable.contains(".")) {
                String[] names = sourceTable.split("\\.", 2);
                schema = names[0].replaceAll("\"", "");
                table = names[1].replaceAll("\"", "");
                originTable = names[1];
            }
            String tableType = null;
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt =
                     createPreparedStatement(connection, checkExternal, table, schema);
                 ResultSet rs = pstmt.executeQuery()) {
                while (Objects.nonNull(rs) && rs.next()) {
                    tableType = rs.getString("relkind");
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
            String dropTable = "drop table " + sourceTable;
            if (StringUtils.isNotBlank(tableType) && tableType.equalsIgnoreCase("f")) {
                dropTable = "drop FOREIGN table " + sourceTable;
            }
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt = connection.prepareStatement(dropTable);
                 ResultSet rs = pstmt.executeQuery()) {
            } catch (SQLException e) {
                log.error(e.getMessage());
            }

            String renameSQL = "ALTER TABLE " + targetTable + " RENAME TO " + originTable;
            log.info("重新命名:" + renameSQL);
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt = connection.prepareStatement(renameSQL);
                 ResultSet resultSet = pstmt.executeQuery()) {
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
    }

    @Override
    public void dropTmpTable(OfflineSyncRedisDomain offlineSyncRedisDomain) {
        Long dsId = offlineSyncRedisDomain.getDsId();
        String dsType = offlineSyncRedisDomain.getDsType();
        String database = offlineSyncRedisDomain.getDatabase();
        String targetTable = offlineSyncRedisDomain.getTargetTable();
        BaseDbManager dbManager = null;
        if (DSType.PostgreSQL.name().equalsIgnoreCase(dsType)) {
            DataSourceDetailDomain dataSourceDetailById =
                dataSourceService.getDataSourceDetailById(dsId, false);
            dbManager =
                getDbManager(
                    dsType,
                    dataSourceDetailById.getUsername(),
                    dataSourceDetailById.getPassword(),
                    dataSourceDetailById.getDsLink());

            String table = null;
            String schema = null;
            if (targetTable.contains(".")) {
                String[] names = targetTable.split("\\.", 2);
                schema = names[0].replaceAll("\"", "");
                table = names[1].replaceAll("\"", "");
            }
            String checkExternal =
                "SELECT relname, relkind\n"
                    + "FROM pg_class\n"
                    + "WHERE relname = ? \n"
                    + "AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname =? )\n";
            String tableType = null;
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt =
                     createPreparedStatement(connection, checkExternal, table, schema);
                 ResultSet rs = pstmt.executeQuery()) {
                while (Objects.nonNull(rs) && rs.next()) {
                    tableType = rs.getString("relkind");
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
            String dropTable = "drop table " + targetTable;
            if (StringUtils.isNotBlank(tableType) && tableType.equalsIgnoreCase("f")) {
                dropTable = "drop FOREIGN table " + targetTable;
            }
            try (Connection connection = dbManager.getConnection();
                 PreparedStatement pstmt = connection.prepareStatement(dropTable);
                 ResultSet rs = pstmt.executeQuery()) {
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
    }

    @Override
    public String getOfflineValue(String key) {
        String s = redisTemplate.opsForValue().get(key);
        return s;
    }

    @Override
    public boolean isMemoryUsageBelowThreshold() {
        com.sun.management.OperatingSystemMXBean osBean = (com.sun.management.OperatingSystemMXBean) ManagementFactory.getOperatingSystemMXBean();
        Integer memoryShold = availableMemoryUsed;
        // 获取总内存和可用内存
        long totalMemorySize = osBean.getTotalPhysicalMemorySize();
        long freeMemorySize = osBean.getFreePhysicalMemorySize() + osBean.getFreeSwapSpaceSize();
        // 计算可用内存与总内存的比例
        double memoryUsagePercentage = ((double) (freeMemorySize) / totalMemorySize) * 100;
        if (memoryUsagePercentage < memoryShold) {
            return false;
        }
        return true;
    }

    private BaseDbManager getDbManager(
        String storageType,
        String username,
        String password,
        String storageLink,
        String hiveSiteXmlPath,
        String schema) {
        Database database = Database.from(storageType);
        DbOptions dbOptions = new DbOptions(database, username, password, storageLink, hiveSiteXmlPath);
        dbOptions.setSchema(schema);
        return DbManagerFactory.getDbManager(database, dbOptions);
    }

    private BaseDbManager getDbManager(
        String storageType, String username, String password, String dsLink) {
        Database database = Database.from(storageType);
        DbOptions dbOptions = new DbOptions(database, dsLink, username, password);
        return DbManagerFactory.getDbManager(database, dbOptions);
    }

    private List<FieldInfoDomain> getFiledInfosByTblName(Long projectId, String tblName) {
        BaseDbManager dbManager = getStorageDbManagerByProjctId(projectId);
        StorageEntity storage = getStorage(projectId);
        List<FieldInfoDomain> list = Lists.newArrayList();
        try {
            if (dbManager instanceof PostgreSqlManager) {
                list = dbManager.getFieldList(storage.getRmdbs().getSchema(), tblName);
            } else {
                list = dbManager.getFieldList(storage.getRmdbs().getDbName(), tblName);
            }
        } catch (Exception e) {

        } finally {
            dbManager.close();
        }
        return list;
    }
}
